﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

using System.Configuration;
using System.Web.Configuration;
using System.Text;
using Biztalk_Alerts.Models;

namespace Biztalk_Alerts.DBOps
{
    public class BTOPerations
    {
        public static ConnectionStringSettings connStrings = null;
        static BTOPerations()
        {
             connStrings =
             WebConfigurationManager.ConnectionStrings["BiztalkConnection"];
        }
        public static IList<Applications> getApplications()
        {
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<Applications> apps = null;
            query.Append("select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application");

            using (SqlConnection conn = new SqlConnection(connStrings.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader=  cmd.ExecuteReader(CommandBehavior.Default);
                apps = new List<Applications>();
                
                while(reader.Read())
                {
                    apps.Add(new Applications() { Description = Convert.ToString(reader["nvcDescription"]), nID = Convert.ToString(reader["nID"]), nvcName = Convert.ToString(reader["nvcName"]) });                    
                }                
                conn.Close();
            }

            return apps;
        }

        public static IList<ReceiveLocations> fetchReceiveLocations(string applicationName)
        {
            //select nID, nvcName, nvcDescription from BizTalkMgmtDb.dbo.bts_application            
            StringBuilder query = new StringBuilder();
            List<ReceiveLocations> locations = null;
            query.Append("SELECT RL.Name AS ReceiveLocationName, RL.InboundTransportURL AS InboundTransportURL, RP.nvcName AS ReceivePortName, RL.Disabled AS SendPortDisabled FROM BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK) INNER JOIN BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK) ON RL.ReceivePortId = RP.nID INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK) ON RP.nApplicationID = APP.nID AND app.nvcName = '" + applicationName + "'");

            using (SqlConnection conn = new SqlConnection(connStrings.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<ReceiveLocations>();

                while (reader.Read())
                {
                    locations.Add(new ReceiveLocations() { InboundTransportURL = Convert.ToString(reader["InboundTransportURL"]),
                                                           ReceiveLocationName = Convert.ToString(reader["ReceiveLocationName"]),
                                                           ReceivePortName = Convert.ToString(reader["ReceivePortName"]),
                                                           SendPortDisabled = Convert.ToBoolean(reader["SendPortDisabled"])        
                });
                }
                conn.Close();
            }

            return locations;
        }

        public static IList<SendLoctions> fetchSendLocations(string appName)
        {
            List<SendLoctions> locations = null;

            StringBuilder query = new StringBuilder();
            query.Append("SELECT APP.nvcName AS AppName, SP.nvcName AS SendLocationName, case SP.nPortStatus WHEN 3 THEN 'Started' WHEN 1 THEN 'Unenlisted' WHEN 2 THEN 'Stopped' WHEN 4 THEN 'Enabled' WHEN 5 THEN 'Disabled' END AS PortStatus, SPTP.nvcAddress AS OutboundTransportURL FROM BizTalkMgmtDb.dbo.bts_sendport AS SP INNER JOIN  BizTalkMgmtDb.dbo.bts_sendport_transport AS SPTP WITH(READPAST, ROWLOCK) ON SPTP.nSendPortID = SP.nID AND SPTP.nSendHandlerID IS NOT NULL INNER JOIN BizTalkMgmtDb.dbo.bts_application AS APP WITH(READPAST, ROWLOCK) ON SP.nApplicationID = APP.nID AND APP.nvcName = '" + appName + "'");

            using (SqlConnection conn = new SqlConnection(connStrings.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(query.ToString(), conn);
                SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                locations = new List<SendLoctions>();

                while (reader.Read())
                {
                    locations.Add(new SendLoctions()
                    {
                        SendLocationName = Convert.ToString(reader["SendLocationName"]),
                        OutboundTransportURL = Convert.ToString(reader["OutboundTransportURL"]),
                        PortStatus = Convert.ToString(reader["PortStatus"]),
                        ApplicationName = Convert.ToString(reader["AppName"])
                    });
                }
                conn.Close();
            }

            return locations;
        }

    }
}